package com.htht.smartschool.service.course;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.common.base.Strings;
import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.course.CourseData;
import com.htht.smartschool.model.course.TCourse;
import com.htht.smartschool.model.course.TCourseorder;
import com.htht.smartschool.model.course.TCoursesubject;
import com.sun.org.apache.bcel.internal.generic.RETURN;
import com.htht.smartschool.util.system.ResultSetToExcel;

public class CourseService extends BaseService{

	/**
	 * 根据学生信息获取课程表
	 * @param studentcode
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getCourseByStudentCode(TCourse tCourse) {
		String sql = "SELECT tc.id, tc.coursecode, tc.classcode, tco.coursecontent, tc.courseno, tc.weekno, tc.semester, tc.coursetime, tc.subjectcode "
				+ " FROM t_course tc, t_coursesubject tco WHERE tc.subjectcode = tco.subjectcode AND tc.classcode = :classcode AND tco.schoolcode = :schoolcode "
				+ " ORDER BY weekno,courseno ASC";
		String classSql = "SELECT tc.grade,tc.class,tt.name,tc.teachercode from t_class tc LEFT JOIN t_teacher tt ON tc.teachercode =  tt.teachercode WHERE tc.classcode = :classcode AND tc.schoolcode = :schoolcode";
		List<Object[]> list =  getDao().queryBySQL(sql,tCourse);
		List<Object[]> list2 = new ArrayList<Object[]>();
		List<Object[]> classlist =  getDao().queryBySQL(classSql,tCourse);
		List<TCourse> result = new ArrayList<TCourse>();
		if(classlist.size() == 0){
			return null;
		}else{
			Object[] calss = classlist.get(0);
			CourseData cd = new CourseData();
			cd.setCalssName(String.valueOf(calss[0])+"年级"+String.valueOf(calss[1])+"班");
			cd.setTeacher(String.valueOf(calss[2]));
			cd.setTeachercode(String.valueOf(calss[3]));
			
			for(int k = 0; k < 8; k++){
				Object[] arr = new Object[7];
				for(int j = 0; j < arr.length; j ++){
					arr[j] = "";
				}
				list2.add(arr);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] obj = list.get(i);
				TCourse course = new TCourse();
				course.setId(Long.valueOf(String.valueOf(obj[0])));
				course.setCoursecode(String.valueOf(obj[1]));
				course.setClasscode(String.valueOf(obj[2]));
				course.setSubjectcode(String.valueOf(obj[3]) == "null" ? "" : String.valueOf(obj[3]));
				course.setCourseno(Integer.parseInt(String.valueOf(obj[4])));
				course.setWeekno(Integer.parseInt(String.valueOf(obj[5])));
				course.setSemester(Integer.parseInt(String.valueOf(obj[6])));
				course.setCoursetime(String.valueOf(obj[7]));
				course.setSubjectcodeno(String.valueOf(obj[8]));
				result.add(course);
				
				int cno = Integer.parseInt(String.valueOf(obj[4])) - 1;
				int wno = Integer.parseInt(String.valueOf(obj[5])) + 1;
				
				Object[] obj2 = list2.get(cno);
				obj2[0] = cno + 1;
				obj2[1] = String.valueOf(obj[7]);
				obj2[wno] = String.valueOf(obj[3]) == "null" ? "" : String.valueOf(obj[3]);
			}
			cd.setCourse(result);
			ResultSetToExcel rse = null;
			if(tCourse.getIsdownload() != null && tCourse.getIsdownload().equalsIgnoreCase("true")){
				rse = new ResultSetToExcel();
				rse.setColoumItem("课程号,时间,星期一,星期二,星期三,星期四,星期五");
//				rse.setColoumItem("id,coursecode,classcode,coursecontent,courseno,weekno,semester,coursetime,subjectcode");
				rse.setRs(list2);
				rse.writeExcel();
			}
			Map map = new HashMap();
			map.put("result", cd);
			if(rse != null){
				rse.setRs(null);
				map.put("remark", rse);
			}
			return map;
		}
	}
	@SuppressWarnings("unchecked")
	public List<TCoursesubject> selectAllCourseSubject(String type,String schoolcode){
		String hql = "from  TCoursesubject where 1 = 1";
		if(type != null){
			hql += "  and type = :type";
		}
		if(schoolcode != null){
			hql += " and schoolcode = :schoolcode";
		}
		TCoursesubject tc = new TCoursesubject();
		tc.setType(type);
		tc.setSchoolcode(schoolcode);
		List<TCoursesubject> list = getDao().queryByHQL(hql,tc);
		return list;
	}
	//批量添加课程表
	public List<TCourse> addOrUpdate(List<TCourse> courseList){
		List<TCourse> result = new ArrayList<TCourse>();
		String sql = "";
		String classcode = courseList.get(0).getClasscode();
		String delSql = "delete from t_course where classcode = '"+classcode+"'";
		getDao().executeSQL(delSql);
		for (TCourse tcourse:courseList) {
			int f = 0;
			TCourse course = dealCourse(tcourse);
			if(course != null){
				sql = "update t_course set ";
				sql += getCourseUpdateSql(course) + " where id = '"+course.getId()+"'";
				f = getDao().executeSQL(sql);
			}else{
				sql = "insert into t_course ";
				sql += getCourseInsertSql(tcourse);
				f = getDao().executeSQL(sql);
			}
			tcourse.setFlag(f == -1 ? false : true);
			result.add(course);
		}
		return courseList;
	}
	public String getCourseInsertSql(TCourse tcourse){
		String sql = "";
		String keys = "";
		String values = "";
		if (tcourse != null) {
			for (Field f : tcourse.getClass().getDeclaredFields()) {
			    f.setAccessible(true);
			    try {
					if (f.get(tcourse) != null && f.get(tcourse) != "" && !f.getName().equalsIgnoreCase("serialVersionUID") && !f.getName().equalsIgnoreCase("flag")) {
						keys += f.getName()+",";
						values += "'"+String.valueOf(f.get(tcourse))+"',";
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			sql = "("+keys.substring(0,keys.length()-1)+") values "+"("+values.substring(0,values.length()-1)+")";
			return sql;
		}else{
			return null;
		}
	}
	public String getCourseUpdateSql(TCourse tcourse){
		String sql = "";
		if (tcourse != null) {
			for (Field f : tcourse.getClass().getDeclaredFields()) {
			    f.setAccessible(true);
			    try {
			    	if (f.get(tcourse) != null && f.get(tcourse) != "" && !f.getName().equalsIgnoreCase("serialVersionUID") && !f.getName().equalsIgnoreCase("flag")) {
						sql +=f.getName() +" = '"+f.get(tcourse)+"' , ";
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			return sql.substring(0,sql.length()-2);
		}else{
			return null;
		}
	}
	public TCourse dealCourse(TCourse tcourse){
		TCourse  course = getRepeat(tcourse.getClasscode(),tcourse.getWeekno(),tcourse.getCourseno());
		if (course != null) {
			for (Field f : tcourse.getClass().getDeclaredFields()) {
			    f.setAccessible(true);
			    try {
					if (f.get(tcourse) != null && !f.getName().equalsIgnoreCase("serialVersionUID")) {
					   f.set(course, f.get(tcourse));
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			return course;
		}else{
			return null;
		}
	}
	//查询数据库是否有学期课程
	@SuppressWarnings("rawtypes")
	public boolean selectRepeat(Integer semester,String classCode){
		boolean flag = false;
		TCourse tcourse = new TCourse();
		tcourse.setSemester(semester);
		tcourse.setClasscode(classCode);
		String sql = "select * from t_course where semester = :semester and classcode = :classcode ";
		List list = getDao().queryBySQL(sql,tcourse);
		if(list.size()>0){
			flag = true;
		}else{
			flag = false;
		}
		return flag;
	}
	@SuppressWarnings("unchecked")
	public TCourse getRepeat(String classcode,Integer weekno,Integer courseno){
		TCourse tcourse = new TCourse();
		tcourse.setClasscode(classcode);
		tcourse.setWeekno(weekno);
		tcourse.setCourseno(courseno);
		String sql = "select * from t_course tc WHERE tc.classcode = :classcode AND tc.weekno  =:weekno AND tc.courseno = :courseno";
		List<Object[]> list = getDao().queryBySQL(sql,tcourse);
		if(list.size() > 0){
			Object[] obj = (Object[]) list.get(0);
			tcourse.setId(Long.valueOf(String.valueOf(obj[0])));
			tcourse.setCoursecode(String.valueOf(obj[1]));
			tcourse.setClasscode(String.valueOf(obj[2]));
			tcourse.setSubjectcode(String.valueOf(obj[3]));
			tcourse.setCourseno(Integer.parseInt(String.valueOf(obj[4])));
			tcourse.setCoursetime(String.valueOf(obj[5]));
			tcourse.setWeekno(Integer.parseInt(String.valueOf(obj[6])));
			tcourse.setSemester(Integer.parseInt(String.valueOf(obj[7])));
			return tcourse;
		}else{
			return null;
		}
		
	}
	//删除重复课程
	public boolean delCourse(Integer semester,String classCode){
		boolean flag = false;
		TCourse tcourse = new TCourse();
		tcourse.setClasscode(classCode);
		tcourse.setSemester(semester);
		String sql = "delete from t_course where semester =:semester and classcode = :classcode";
		int i = getDao().executeSQL(sql,tcourse);
		if(i>0){
			flag = true;
		}else{
			flag = false;
		}
		return flag;
	}
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List getCoursePlan(String classcode) {
		TCourse tcourse = new TCourse();
		tcourse.setClasscode(classcode);
		String sql = "SELECT DISTINCT cv.coursecode,cv.coursecontent,tco.teachercode,tt.name "
				+ "FROM course_view cv,t_courseorder tco,t_teacher tt "
				+ "WHERE cv.coursecode = tco.coursecode AND tco.teachercode = tt.teachercode AND cv.classcode = :classcode";
		List result = new ArrayList();
		List<Object[]> list = getDao().queryBySQL(sql,tcourse);
		for (Object[] obj : list) {
			Map<String, String> map = new HashMap<String, String>();
			map.put("coursecode", String.valueOf(obj[0]));
			map.put("coursecontent", String.valueOf(obj[1]));
			map.put("teachercode", String.valueOf(obj[2]));
			map.put("name", String.valueOf(obj[3]));
			result.add(map);
		}
		String nullsql ="SELECT DISTINCT cv.coursecode,cv.coursecontent "
			+"FROM course_view cv "
			+"WHERE cv.classcode = :classcode";
		List resultnew = new ArrayList();
		List<Object[]> nulllist = getDao().queryBySQL(nullsql,tcourse);
		for (Object[] obj : nulllist) {
			Map<String, String> map = new HashMap<String, String>();
			map.put("coursecode", String.valueOf(obj[0]));
			map.put("coursecontent", String.valueOf(obj[1]));
			map.put("teachercode", "");
			map.put("name", "");
			boolean flag = true;
			for (int i = 0; i < result.size(); i++) {
				Map<String, String> mapc = (Map<String, String>) result.get(i);
				if(mapc.get("coursecontent").equalsIgnoreCase(String.valueOf(obj[1]))){
					flag = false;
					break;
				}
			}
			if(flag){
				resultnew.add(map);
			}
		}
		result.addAll(resultnew);
		return result;
	}
	public boolean addTeahcerForCourse(List<TCourseorder> courseOrderList) {
		boolean flag = true;
		int f = 0;
		for(int i = 0; i < courseOrderList.size(); i++){
			TCourseorder obj = (TCourseorder)courseOrderList.get(i);
			List<TCourseorder> list = getRepeatByCoursecode(obj.getCoursecode());
			if(list.size() == 0){
				String sql = "insert into t_courseorder (teachercode,coursecode) values (:teachercode,:coursecode)";
				f = getDao().executeSQL(sql,obj);
			}else{
				String sql = "update t_courseorder set teachercode = :teachercode where coursecode = :coursecode";
				f = getDao().executeSQL(sql,obj);
			}
			if(f < 0){
				flag = false;
				break;
			}
		}
		return flag;
	}
	@SuppressWarnings("unchecked")
	public List<TCourseorder> getRepeatByCoursecode(String coursecode) {
		List<TCourseorder> list = new ArrayList<TCourseorder>();
		TCourse tcourse = new TCourse();
		tcourse.setCoursecode(coursecode);
		String sql = "select t.* from t_courseorder t where t.coursecode = :coursecode";
		list = getDao().queryBySQL(sql,tcourse);
		return list;
	}
	public boolean addCourseSubjectCode(TCoursesubject tcs) {
		List<TCoursesubject> list = getTCoursesubjectDetatl(tcs.getSubjectcode());
		if(list.size() > 0){
			return false;
		}else{
			String sql = "INSERT INTO t_coursesubject (id,subjectcode,coursecontent,type,schoolcode)"
					+ " VALUES (NULL,:subjectcode,:coursecontent,:type,:schoolcode)";
			int i = getDao().executeSQL(sql,tcs);
			return i == -1 ? false : true;
		}
	}
	public boolean delCourseSubjectCode(TCoursesubject tcs) {
		String sql = "DELETE FROM t_coursesubject WHERE subjectcode = :subjectcode";
		int i = getDao().executeSQL(sql,tcs);
		return i == -1 ? false : true;
	}
	public boolean updateTeahcerForCourse(TCoursesubject tcs) {
		TCoursesubject tCoursesubject = getTCS(tcs);
		String sql = "UPDATE t_coursesubject SET ";
		sql = sql + " coursecontent = :coursecontent,";
		sql = sql + " type = :type";
		sql = sql + " where subjectcode = :subjectcode";
		int i = getDao().executeSQL(sql,tcs);
		return i == -1 ? false : true;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getTeahcerForCourse(TCoursesubject tcs) {
		Map map = new HashMap();
		String hql = "From TCoursesubject where 1 = 1";
		if(tcs.getType() != null && tcs.getType() != ""){
			hql += " and type = :type";
		}
		if(tcs.getSchoolcode() != null && tcs.getSchoolcode().length() > 0){
			hql += " and schoolcode = :schoolcode";
		}
		List<TCoursesubject> list = getDao().queryByHQLPage(hql,tcs, tcs.getCurrentpage(), tcs.getPagesize());
		int count = getDao().getCountByHqlPage(hql,tcs);
		map.put("data", list);
		map.put("total", count);
		return map;
	}
	@SuppressWarnings("unchecked")
	public TCoursesubject getTCS(TCoursesubject tCoursesubject){
		String hql = "From TCoursesubject where subjectcode = :subjectcode";
		List<TCoursesubject> list = getDao().queryByHQL(hql,tCoursesubject);
		if(list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	@SuppressWarnings("unchecked")
	public List<TCoursesubject> getTCoursesubjectDetatl(String subjectcode){
		TCoursesubject tc = new TCoursesubject();
		tc.setSubjectcode(subjectcode);
		String sql = "select * from t_coursesubject where subjectcode = :subjectcode";
		List<TCoursesubject> list = getDao().queryBySQL(sql,tc);
		return list;
	}
	/**添加课程表
	 * @author:wuqiwei
	 * @param @param tc
	 * @param @return
	 * @date:2016年1月26日上午10:58:02
	*/
	public boolean addCourse(TCourse tc) {
		String coursecontent = tc.getCoursecontent();
		TCoursesubject t = getTCoursesubject(coursecontent);
		String coursecode =tc.getClasscode() + tc.getSubjectcode();
		tc.setCoursecode(coursecode);
		//先去查看是否有这门课程
		if(t != null){
			String sql = "update t_coursesubject set coursecontent = :coursecontent where subjectcode = :subjectcode";//更新课程
			getDao().executeSQL(sql,tc);
		}else{
			String sql = "insert into t_coursesubject(subjectcode,coursecontent,type) values(:subjectcode,:coursecontent,:type)";//插入课程
			getDao().executeSQL(sql,tc);
		}
		//去判断有没有这个课程表
		tc.setCoursecode(coursecode);
		TCourse tt = getTcourse(tc);
		if(tt != null){
			String sql = "update t_course set coursecode = :coursecode,classcode = :classcode,subjectcode = :subjectcode,courseno = :courseno,"
					+ "coursetime = :coursetime,weekno = :weekno,semester = :semester";
			getDao().executeSQL(sql,tc);
		}else{
			//添加表t_course信息
			String sql = "insert into t_course(coursecode,classcode,subjectcode,courseno,coursetime,weekno,semester) values(:coursecode,:classcode,:subjectcode,:courseno,:coursetime,:weekno,:semester)";
			getDao().executeSQL(sql,tc);
		}
		String teachercode = tc.getTeachercode();
			//添加关联表t_courseorder
			if(teachercode.length()>0 && teachercode != null){
				String sql1 ="select * from t_courseorder where teachercode = :teachercode and coursecode = :coursecode";
				List<Object> list =getDao().queryBySQL(sql1,tc);
				if(list.size() > 0){
					String sql = "update t_courseorder set teachercode = :teachercode ,coursecode = :coursecode";
					getDao().executeSQL(sql,tc);
				}else{
					TCourseorder tCourseorder = new TCourseorder();
					tCourseorder.setCoursecode(coursecode);
					tCourseorder.setTeachercode(teachercode);
					getDao().create(tCourseorder);
				}
			}
			//添加关联表
//			TCoursesubject tCoursesubject  = new TCoursesubject();
//			tCoursesubject.setSubjectcode(subjectcode);
//			tCoursesubject.setType(semester);
//			tCoursesubject.setCoursecontent(coursecontent);
//			getDao().create(tCoursesubject);
			return true;
	}
	/**查询是否有重复的课程
	 * @author:wuqiwei
	 * @param @param tc
	 * @param @return
	 * @date:2016年1月27日下午5:35:37
	*/
	private TCourse getTcourse(TCourse tc) {
		String hql ="FROM TCourse where coursecode = :coursecode and courseno = :courseno and weekno = :weekno and semester = :semester";
		TCourse tCourse =  (TCourse) getDao().queryFirstByHQL(hql,tc);
		return tCourse;
	}
	/**查看是否有这门课程
	 * @author:wuqiwei
	 * @param @param coursecontent
	 * @param @return
	 * @date:2016年1月26日下午1:46:44
	*/
	private TCoursesubject getTCoursesubject(String coursecontent) {
		TCourse tc = new TCourse();
		tc.setCoursecontent(coursecontent);
		String hql = "FROM TCoursesubject where coursecontent = :coursecontent";
		TCoursesubject tcoursesubjectgetDao=(TCoursesubject) getDao().queryFirstByHQL(hql,tc);
		return tcoursesubjectgetDao;
	}
	
}
